-- ============================================================
-- ARXIV PAPER SUBSCRIPTION PLATFORM - DATABASE SCHEMA
-- ============================================================
-- Version: 1.0
-- Created: 2025-08-20
-- Description: Complete PostgreSQL schema for multi-user ArXiv paper subscription platform
-- Supporting: 10,000+ concurrent users with personalized recommendations and email digests
-- ============================================================

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ============================================================
-- 1. USER MANAGEMENT TABLES
-- ============================================================

-- Users table - Core user information
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL, -- bcrypt hash
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    display_name VARCHAR(150),
    profile_image_url TEXT,
    timezone VARCHAR(50) DEFAULT 'UTC',
    is_active BOOLEAN DEFAULT true,
    is_verified BOOLEAN DEFAULT false,
    email_verification_token VARCHAR(255),
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMP,
    last_login TIMESTAMP,
    login_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL, -- Soft delete for GDPR compliance
    
    CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_timezone_valid CHECK (timezone IN (
        'UTC', 'US/Eastern', 'US/Central', 'US/Mountain', 'US/Pacific',
        'Europe/London', 'Europe/Berlin', 'Europe/Paris', 'Asia/Tokyo',
        'Asia/Shanghai', 'Australia/Sydney'
    ))
);

-- User preferences for personalization
CREATE TABLE user_preferences (
    preference_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    preference_key VARCHAR(100) NOT NULL,
    preference_value JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(user_id, preference_key)
);

-- User sessions for authentication
CREATE TABLE user_sessions (
    session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    ip_address INET,
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User roles and permissions
CREATE TABLE user_roles (
    role_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    role_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    permissions JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_role_assignments (
    assignment_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES user_roles(role_id) ON DELETE CASCADE,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by UUID REFERENCES users(user_id),
    
    UNIQUE(user_id, role_id)
);

-- ============================================================
-- 2. PAPER MANAGEMENT TABLES
-- ============================================================

-- ArXiv categories lookup table
CREATE TABLE arxiv_categories (
    category_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_code VARCHAR(20) UNIQUE NOT NULL, -- e.g., 'cs.AI', 'math.NA'
    category_name VARCHAR(200) NOT NULL,
    parent_category VARCHAR(20),
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Authors table
CREATE TABLE authors (
    author_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    full_name VARCHAR(200) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    affiliation TEXT,
    orcid_id VARCHAR(50),
    google_scholar_id VARCHAR(50),
    arxiv_author_id VARCHAR(50),
    h_index INTEGER,
    citation_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Papers table - Core paper information
CREATE TABLE papers (
    paper_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    arxiv_id VARCHAR(50) UNIQUE NOT NULL, -- e.g., '2501.06145'
    title TEXT NOT NULL,
    abstract TEXT NOT NULL,
    doi VARCHAR(100),
    pdf_url TEXT,
    submission_date DATE NOT NULL,
    publication_date DATE,
    last_updated DATE,
    version VARCHAR(10) DEFAULT 'v1',
    journal_reference TEXT,
    msc_class TEXT, -- Mathematics Subject Classification
    acm_class TEXT, -- ACM Classification
    primary_category VARCHAR(20) NOT NULL,
    secondary_categories TEXT[], -- Array of category codes
    language VARCHAR(10) DEFAULT 'en',
    page_count INTEGER,
    citation_count INTEGER DEFAULT 0,
    download_count INTEGER DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    is_withdrawn BOOLEAN DEFAULT false,
    withdrawal_reason TEXT,
    license VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    indexed_at TIMESTAMP,
    
    CONSTRAINT chk_arxiv_id_format CHECK (arxiv_id ~* '^[0-9]{4}\.[0-9]{4,5}(v[0-9]+)?$')
);

-- Paper-author relationships (many-to-many)
CREATE TABLE paper_authors (
    paper_author_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    author_id UUID NOT NULL REFERENCES authors(author_id) ON DELETE CASCADE,
    author_order INTEGER NOT NULL,
    is_corresponding BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(paper_id, author_id),
    UNIQUE(paper_id, author_order)
);

-- Paper classifications and tags
CREATE TABLE paper_classifications (
    classification_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    classification_type VARCHAR(50) NOT NULL, -- 'arxiv_category', 'keyword', 'topic', 'methodology'
    classification_value VARCHAR(200) NOT NULL,
    confidence_score DECIMAL(3,2), -- 0.00 to 1.00
    is_primary BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(50) DEFAULT 'system' -- 'system', 'user', 'admin'
);

-- Paper metadata and additional information
CREATE TABLE paper_metadata (
    metadata_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    metadata_key VARCHAR(100) NOT NULL,
    metadata_value JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(paper_id, metadata_key)
);

-- Paper citations (references between papers)
CREATE TABLE paper_citations (
    citation_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    citing_paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    cited_paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    citation_context TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(citing_paper_id, cited_paper_id),
    CONSTRAINT chk_no_self_citation CHECK (citing_paper_id != cited_paper_id)
);

-- ============================================================
-- 3. USER INTERACTION TABLES
-- ============================================================

-- User-paper interactions (views, downloads, etc.)
CREATE TABLE user_paper_interactions (
    interaction_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    interaction_type VARCHAR(50) NOT NULL, -- 'view', 'download', 'bookmark', 'share'
    interaction_data JSONB DEFAULT '{}', -- Additional context data
    interaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    session_id UUID REFERENCES user_sessions(session_id),
    ip_address INET,
    user_agent TEXT,
    
    CONSTRAINT chk_interaction_type CHECK (interaction_type IN (
        'view', 'download', 'bookmark', 'unbookmark', 'share', 'rate', 'comment'
    ))
);

-- User bookmarks
CREATE TABLE user_bookmarks (
    bookmark_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    bookmark_folder VARCHAR(100) DEFAULT 'default',
    notes TEXT,
    tags TEXT[],
    is_private BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(user_id, paper_id)
);

-- User ratings for papers
CREATE TABLE user_ratings (
    rating_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    rating INTEGER NOT NULL,
    review_text TEXT,
    is_public BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(user_id, paper_id),
    CONSTRAINT chk_rating_range CHECK (rating >= 1 AND rating <= 5)
);

-- User comments on papers
CREATE TABLE user_comments (
    comment_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES user_comments(comment_id),
    comment_text TEXT NOT NULL,
    is_public BOOLEAN DEFAULT false,
    is_anonymous BOOLEAN DEFAULT false,
    upvote_count INTEGER DEFAULT 0,
    downvote_count INTEGER DEFAULT 0,
    is_flagged BOOLEAN DEFAULT false,
    flagged_reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

-- ============================================================
-- 4. SUBSCRIPTION AND NOTIFICATION TABLES
-- ============================================================

-- User subscriptions to topics, authors, or categories
CREATE TABLE user_subscriptions (
    subscription_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    subscription_type VARCHAR(50) NOT NULL, -- 'category', 'author', 'keyword', 'citation_alert'
    subscription_target VARCHAR(200) NOT NULL, -- Category code, author ID, keyword, etc.
    subscription_name VARCHAR(200), -- User-friendly name
    is_active BOOLEAN DEFAULT true,
    notification_frequency VARCHAR(20) DEFAULT 'daily', -- 'immediate', 'daily', 'weekly', 'monthly'
    last_notification_sent TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_subscription_type CHECK (subscription_type IN (
        'category', 'author', 'keyword', 'citation_alert', 'journal', 'custom_search'
    )),
    CONSTRAINT chk_notification_frequency CHECK (notification_frequency IN (
        'immediate', 'daily', 'weekly', 'monthly', 'never'
    ))
);

-- Email digest generation and tracking
CREATE TABLE email_digests (
    digest_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    digest_type VARCHAR(50) NOT NULL, -- 'daily', 'weekly', 'monthly', 'custom'
    digest_date DATE NOT NULL,
    subject_line VARCHAR(255) NOT NULL,
    html_content TEXT,
    text_content TEXT,
    paper_count INTEGER DEFAULT 0,
    paper_ids UUID[], -- Array of paper IDs included
    generation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_sent BOOLEAN DEFAULT false,
    sent_at TIMESTAMP,
    delivery_status VARCHAR(50) DEFAULT 'pending', -- 'pending', 'sent', 'delivered', 'bounced', 'failed'
    open_count INTEGER DEFAULT 0,
    click_count INTEGER DEFAULT 0,
    unsubscribe_clicked BOOLEAN DEFAULT false,
    
    UNIQUE(user_id, digest_type, digest_date)
);

-- Email delivery tracking
CREATE TABLE email_delivery_logs (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    digest_id UUID REFERENCES email_digests(digest_id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    email_address VARCHAR(255) NOT NULL,
    delivery_status VARCHAR(50) NOT NULL,
    delivery_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    provider_message_id VARCHAR(255),
    provider_response JSONB,
    bounce_reason TEXT,
    retry_count INTEGER DEFAULT 0,
    
    CONSTRAINT chk_delivery_status CHECK (delivery_status IN (
        'queued', 'sent', 'delivered', 'opened', 'clicked', 'bounced', 'failed', 'spam', 'unsubscribed'
    ))
);

-- Notification preferences
CREATE TABLE notification_preferences (
    preference_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    notification_type VARCHAR(50) NOT NULL,
    is_enabled BOOLEAN DEFAULT true,
    delivery_method VARCHAR(20) DEFAULT 'email', -- 'email', 'push', 'sms'
    frequency VARCHAR(20) DEFAULT 'daily',
    quiet_hours_start TIME,
    quiet_hours_end TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(user_id, notification_type, delivery_method)
);

-- ============================================================
-- 5. RECOMMENDATION AND ANALYTICS TABLES
-- ============================================================

-- User paper recommendations
CREATE TABLE user_recommendations (
    recommendation_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    recommendation_score DECIMAL(5,4) NOT NULL, -- 0.0000 to 1.0000
    recommendation_reason VARCHAR(200), -- 'similar_interests', 'trending', 'author_follow'
    algorithm_version VARCHAR(20) DEFAULT 'v1.0',
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_clicked BOOLEAN DEFAULT false,
    clicked_at TIMESTAMP,
    
    UNIQUE(user_id, paper_id, algorithm_version)
);

-- User behavior analytics
CREATE TABLE user_analytics (
    analytics_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    date DATE NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    metric_value DECIMAL(10,4) NOT NULL,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(user_id, date, metric_name)
);

-- Paper analytics and trending data
CREATE TABLE paper_analytics (
    analytics_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    paper_id UUID NOT NULL REFERENCES papers(paper_id) ON DELETE CASCADE,
    date DATE NOT NULL,
    view_count INTEGER DEFAULT 0,
    download_count INTEGER DEFAULT 0,
    bookmark_count INTEGER DEFAULT 0,
    share_count INTEGER DEFAULT 0,
    citation_count INTEGER DEFAULT 0,
    trending_score DECIMAL(8,4) DEFAULT 0.0000,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(paper_id, date)
);

-- ============================================================
-- 6. SYSTEM AND AUDIT TABLES
-- ============================================================

-- System configuration
CREATE TABLE system_config (
    config_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value JSONB NOT NULL,
    description TEXT,
    is_encrypted BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Audit log for GDPR compliance
CREATE TABLE audit_logs (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    table_name VARCHAR(100) NOT NULL,
    record_id UUID NOT NULL,
    user_id UUID REFERENCES users(user_id),
    action VARCHAR(50) NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE', 'SELECT'
    old_values JSONB,
    new_values JSONB,
    ip_address INET,
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_action_type CHECK (action IN ('INSERT', 'UPDATE', 'DELETE', 'SELECT', 'EXPORT'))
);

-- API usage tracking
CREATE TABLE api_usage_logs (
    log_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID REFERENCES users(user_id),
    endpoint VARCHAR(200) NOT NULL,
    method VARCHAR(10) NOT NULL,
    status_code INTEGER NOT NULL,
    response_time_ms INTEGER,
    request_size INTEGER,
    response_size INTEGER,
    ip_address INET,
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    rate_limit_remaining INTEGER,
    
    CONSTRAINT chk_http_method CHECK (method IN ('GET', 'POST', 'PUT', 'DELETE', 'PATCH', 'OPTIONS'))
);

-- Background job tracking
CREATE TABLE background_jobs (
    job_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    job_type VARCHAR(100) NOT NULL,
    job_status VARCHAR(50) DEFAULT 'pending',
    job_data JSONB DEFAULT '{}',
    scheduled_at TIMESTAMP NOT NULL,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    max_retries INTEGER DEFAULT 3,
    created_by UUID REFERENCES users(user_id),
    
    CONSTRAINT chk_job_status CHECK (job_status IN ('pending', 'running', 'completed', 'failed', 'cancelled'))
);

-- ============================================================
-- TRIGGERS FOR UPDATED_AT COLUMNS
-- ============================================================

-- Function to update updated_at column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply triggers to tables with updated_at columns
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_preferences_updated_at BEFORE UPDATE ON user_preferences
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_papers_updated_at BEFORE UPDATE ON papers
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_authors_updated_at BEFORE UPDATE ON authors
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_paper_metadata_updated_at BEFORE UPDATE ON paper_metadata
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_bookmarks_updated_at BEFORE UPDATE ON user_bookmarks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_ratings_updated_at BEFORE UPDATE ON user_ratings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_comments_updated_at BEFORE UPDATE ON user_comments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_subscriptions_updated_at BEFORE UPDATE ON user_subscriptions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_notification_preferences_updated_at BEFORE UPDATE ON notification_preferences
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_system_config_updated_at BEFORE UPDATE ON system_config
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- PERFORMANCE INDEXES
-- ============================================================

-- User-related indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(is_active) WHERE is_active = true;
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_last_login ON users(last_login);
CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_user_sessions_token ON user_sessions(session_token);
CREATE INDEX idx_user_sessions_expires ON user_sessions(expires_at);
CREATE INDEX idx_user_preferences_user_key ON user_preferences(user_id, preference_key);

-- Paper-related indexes
CREATE INDEX idx_papers_arxiv_id ON papers(arxiv_id);
CREATE INDEX idx_papers_submission_date ON papers(submission_date DESC);
CREATE INDEX idx_papers_primary_category ON papers(primary_category);
CREATE INDEX idx_papers_title_gin ON papers USING gin(to_tsvector('english', title));
CREATE INDEX idx_papers_abstract_gin ON papers USING gin(to_tsvector('english', abstract));
CREATE INDEX idx_papers_updated_at ON papers(updated_at DESC);
CREATE INDEX idx_papers_citation_count ON papers(citation_count DESC);
CREATE INDEX idx_papers_view_count ON papers(view_count DESC);

-- Author-related indexes
CREATE INDEX idx_authors_name ON authors(full_name);
CREATE INDEX idx_authors_email ON authors(email);
CREATE INDEX idx_authors_orcid ON authors(orcid_id);
CREATE INDEX idx_paper_authors_paper_id ON paper_authors(paper_id);
CREATE INDEX idx_paper_authors_author_id ON paper_authors(author_id);
CREATE INDEX idx_paper_authors_order ON paper_authors(paper_id, author_order);

-- Classification indexes
CREATE INDEX idx_paper_classifications_paper_id ON paper_classifications(paper_id);
CREATE INDEX idx_paper_classifications_type_value ON paper_classifications(classification_type, classification_value);
CREATE INDEX idx_paper_classifications_primary ON paper_classifications(paper_id, is_primary) WHERE is_primary = true;

-- Interaction indexes
CREATE INDEX idx_user_interactions_user_id ON user_paper_interactions(user_id);
CREATE INDEX idx_user_interactions_paper_id ON user_paper_interactions(paper_id);
CREATE INDEX idx_user_interactions_type_date ON user_paper_interactions(interaction_type, interaction_date DESC);
CREATE INDEX idx_user_interactions_user_date ON user_paper_interactions(user_id, interaction_date DESC);

-- Bookmark indexes
CREATE INDEX idx_user_bookmarks_user_id ON user_bookmarks(user_id);
CREATE INDEX idx_user_bookmarks_paper_id ON user_bookmarks(paper_id);
CREATE INDEX idx_user_bookmarks_folder ON user_bookmarks(user_id, bookmark_folder);
CREATE INDEX idx_user_bookmarks_created ON user_bookmarks(created_at DESC);

-- Subscription indexes
CREATE INDEX idx_user_subscriptions_user_id ON user_subscriptions(user_id);
CREATE INDEX idx_user_subscriptions_type_target ON user_subscriptions(subscription_type, subscription_target);
CREATE INDEX idx_user_subscriptions_active ON user_subscriptions(is_active) WHERE is_active = true;

-- Email digest indexes
CREATE INDEX idx_email_digests_user_id ON email_digests(user_id);
CREATE INDEX idx_email_digests_date ON email_digests(digest_date DESC);
CREATE INDEX idx_email_digests_status ON email_digests(delivery_status);
CREATE INDEX idx_email_digests_sent ON email_digests(sent_at DESC) WHERE is_sent = true;

-- Recommendation indexes
CREATE INDEX idx_user_recommendations_user_id ON user_recommendations(user_id);
CREATE INDEX idx_user_recommendations_score ON user_recommendations(recommendation_score DESC);
CREATE INDEX idx_user_recommendations_generated ON user_recommendations(generated_at DESC);
CREATE INDEX idx_user_recommendations_expires ON user_recommendations(expires_at);

-- Analytics indexes
CREATE INDEX idx_user_analytics_user_date ON user_analytics(user_id, date DESC);
CREATE INDEX idx_user_analytics_metric ON user_analytics(metric_name, date DESC);
CREATE INDEX idx_paper_analytics_paper_date ON paper_analytics(paper_id, date DESC);
CREATE INDEX idx_paper_analytics_trending ON paper_analytics(trending_score DESC, date DESC);

-- Audit and logging indexes
CREATE INDEX idx_audit_logs_table_record ON audit_logs(table_name, record_id);
CREATE INDEX idx_audit_logs_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_timestamp ON audit_logs(timestamp DESC);
CREATE INDEX idx_api_usage_user_id ON api_usage_logs(user_id);
CREATE INDEX idx_api_usage_timestamp ON api_usage_logs(timestamp DESC);
CREATE INDEX idx_api_usage_endpoint ON api_usage_logs(endpoint, timestamp DESC);

-- Background job indexes
CREATE INDEX idx_background_jobs_status ON background_jobs(job_status);
CREATE INDEX idx_background_jobs_scheduled ON background_jobs(scheduled_at);
CREATE INDEX idx_background_jobs_type_status ON background_jobs(job_type, job_status);

-- ============================================================
-- COMMENTS FOR DOCUMENTATION
-- ============================================================

COMMENT ON TABLE users IS 'Core user accounts with authentication and profile information';
COMMENT ON TABLE papers IS 'ArXiv papers with metadata and classification information';
COMMENT ON TABLE user_paper_interactions IS 'Tracks all user interactions with papers for analytics and recommendations';
COMMENT ON TABLE user_subscriptions IS 'User subscriptions to categories, authors, keywords for personalized notifications';
COMMENT ON TABLE email_digests IS 'Generated email digests with content and delivery tracking';
COMMENT ON TABLE user_recommendations IS 'ML-generated paper recommendations for users';
COMMENT ON TABLE audit_logs IS 'GDPR-compliant audit trail for all database operations';

-- ============================================================
-- END OF SCHEMA
-- ============================================================